<?php
	require_once('config.inc.php');
	//include('/include/functions/initial_schedule.php');
	
	
	function initial_schedule ($semester_id, $building_id, $schedule_graduate, $schedule_honors, $schedule_levels, $strict_classroom_type)
	{
		/*get subjects for this bulding*/
		$subjectQuery = "SELECT cs.id
						 FROM course_subjects cs
						 JOIN building_subject_mapping bsm ON bsm.course_subject_id = cs.id
						 WHERE building_id = '$building_id'
						 AND priority = '1'";
		$subjectResult = mysql_query($subjectQuery);
		/*iterate through each subject*/
		while ($subjectRow = mysql_fetch_assoc($subjectResult))
		{
			$subject_id = $subjectRow['id'];
			
			/*get all unscheduled sections for this subject*/
			$sectionQuery = "SELECT *, csec.id AS course_section_id, MIN(graduate_level) AS grad_level
							 FROM course_sections csec
							 JOIN course_section_mapping csm ON csm.course_section_id = csec.id
							 JOIN courses c on c.id = csm.course_id
							 JOIN course_subjects csub ON csub.id = c.course_subject_id
							 WHERE semester_id = '$semester_id'
							 AND scheduling_rank = '1'
							 AND csub.id = '$subject_id'
							 AND csec.id NOT IN (SELECT course_section_id
							 					 FROM schedule)";
			/*if $schedule_graduate == 1, limit results to graduate level ONLY courses*/
			if ($schedule_graduate)
			{
				$sectionQuery .= " AND graduate_level = '1'";
			}
			/*if $schedule_graduate == 0, limit results to courses which contain undergad 
				(and possibly grad) level courses*/
			else
			{
				$sectionQuery .= " AND graduate_level = '0'";
			}
			/*if $schedule_honors == 1, limit results to honors program courses */
			if ($schedule_honors)
			{
				$sectionQuery .= " AND honors_program = '1'";
			}
			/*this ensures that each section only returns one row, regardless of the numebr of
				courses mapped to that section*/
			$sectionQuery .= " GROUP BY csec.id
							   ORDER BY course_number DESC";
			$sectionResult = mysql_query($sectionQuery);
			//echo mysql_error();
			//echo $sectionQuery."<br /><br />".mysql_error();
			/*iterate through each section*/
			while ($sectionRow = mysql_fetch_assoc($sectionResult))
			{
				$course_section_id = $sectionRow['course_section_id'];
				
				if ($schedule_graduate)
				{
					$gradQuery = "SELECT MIN(course_number) as course_num
								  FROM courses c
								  JOIN course_section_mapping csm ON csm.course_id = c.id
								  JOIN course_sections cs ON cs.id = csm.course_section_id
								  WHERE cs.id = '$course_section_id'";
					$gradResult = mysql_query($gradQuery);
					$gradRow = mysql_fetch_assoc($gradResult);
					if ($gradRow['course_num'] <= 4999)
					{
						continue;
					}
				}
				
				/*echo "<pre>";
				print_r($sectionRow);
				echo "</pre>";
				*/
				
				$course_number = $sectionRow['course_number'];
				$classroom_type_id = $sectionRow['classroom_type_id'];
				$hours_per_week = $sectionRow['hours_per_week'];
				$num_seats = $sectionRow['num_seats'];
				/*if instrcutor_id == 3, instructor is TBA*/
				$instructor_id = $sectionRow['instructor_id'];
				$availability = array();
				//echo "<h4>$instructor_id</h4>";
				
				$cDescription = "<strong>Now attempting to schedule course section.</strong>";
				$cDescription = mysql_real_escape_string($cDescription);
				$date_time = date('Y-m-d H:i:s');
				$collisionQuery = "INSERT INTO collision_log (id, section_id, description, date_time)
								   VALUES ('', '$course_section_id', '$cDescription', '$date_time')";
				$collisonResult = mysql_query($collisionQuery);
				//echo "<br/><br/>".$collisionQuery."<br/>".mysql_error()."<br/><br/>";
					
				$availQuery = "SELECT *
							   FROM instructor_availability ia
							   JOIN days ON days.id = ia.day_id
							   WHERE instructor_id = '$instructor_id'
							   ORDER by day_id";
				$availResult = mysql_query($availQuery);
				//echo mysql_error();
				$count = 0;
				$last_day_id = 0;
				while ($availRow = mysql_fetch_assoc($availResult))
				{
					if ($availRow['day_id'] != $last_day_id)
					{
						$count = 0;
					}
					$count++;
					
					$day_name = $availRow['day_name'];
					$start_time = $availRow['start_time'];
					$end_time = $availRow['end_time'];
					
					$availability[$day_name][$count]['start_time'] = $start_time;
					$availability[$day_name][$count]['end_time'] = $end_time;
					
					$last_day_id = $availRow['day_id'];
				}
				
				/*
				echo "<pre>";
				print_r($availability);
				echo "</pre>";
				*/
				
				/*get all timeslots from the current building that match the number of hours per week*/
				for ($x = 0; $x < 2; $x++)
				{
					$timeslotQuery = "SELECT *
									  FROM time_slots ts";
					if ($x == 0)
					{
						$timeslotQuery .= " LEFT JOIN time_slot_preferences tsp ON tsp.time_slot_id = ts.id";
					}
					$timeslotQuery .= " WHERE building_id = '$building_id'
									  AND hours_per_week = '$hours_per_week'";
					if ($x == 0)
					{
						$timeslotQuery .= " AND instructor_id = '$instructor_id'";
					}
					if ($schedule_graduate)
					{
						$timeslotQuery .= " AND start_time >= '17:00:00'";
					}
					if ($x == 0)
					{
						$timeslotQuery .= " ORDER BY rank_of_preference ASC";
					}
					$timeslotResult = mysql_query($timeslotQuery);
					$valid_timeslots = array();
					
					$date_time = date('Y-m-d H:i:s');
					$collisionQuery = "INSERT INTO collision_log (id, section_id, timeslot_id, description, date_time) VALUES ";
					
					/*loop through each timelsot*/
					while($timeslotRow = mysql_fetch_assoc($timeslotResult))
					{
						$ts_start_time = $timeslotRow['start_time'];
						$ts_end_time = $timeslotRow['end_time'];
						$Monday = $timeslotRow['Monday'];
						$Tuesday = $timeslotRow['Tuesday'];
						$Wednesday = $timeslotRow['Wednesday'];
						$Thursday = $timeslotRow['Thursday'];
						$Friday = $timeslotRow['Friday'];
						$Saturday = $timeslotRow['Saturday'];
						$Sunday = $timeslotRow['Sunday'];
						$ts_days = array('Monday' => $Monday, 'Tuesday' => $Tuesday, 'Wednesday' => $Wednesday, 'Thursday' => $Thursday, 'Friday' => $Friday, 'Saturday' => $Saturday, 'Sunday' => $Sunday);
						
						$valid_timeslot = TRUE;
						$cReason = '';
						$aToken = FALSE;
						$hToken = FALSE;
						$LToken = FALSE;
						$hasLabToken = FALSE;
						$isLabToken = FALSE;
						
						/*go through each day of the week, checking to see if the timeslot falls within
						 the instructor's availability for that day and checking to ensure the timeslot
						 does not overlap with a course that's already scheduled for that instructor*/
						foreach ($ts_days as $day => $val)
						{
							$valid_for_this_day = TRUE;
							if ($val == '1')
							{
								$valid_for_this_day = FALSE;
								foreach ($availability[$day] as $avail_slot)
								{
									if ($ts_start_time >= $avail_slot['start_time'] && $ts_end_time <= $avail_slot['end_time'])
									{
										$valid_for_this_day = TRUE;
									}
								}
								
								if (!$valid_for_this_day)
								{
									$cReason .= "does not fit into instructor's availability;<br/>";
								}
								
								//check if the instructor is already scheduled for a time that overlaps this timeslot
								if ($instructor_id != '1')
								{
									$alreadyScheduledQuery = "SELECT *
															  FROM schedule 
															  JOIN course_sections cs ON cs.id = schedule.course_section_id
															  JOIN time_slots ts ON ts.id = schedule.time_slot_id
															  WHERE instructor_id = '$instructor_id'
															  AND semester_id = '$semester_id'";
									$alreadyScheduledResult = mysql_query($alreadyScheduledQuery);
									while ($asRow = mysql_fetch_assoc($alreadyScheduledResult))
									{
										if ($asRow[$day] == 1)
										{
											if (($asRow['start_time'] < $ts_start_time && $asRow['end_time'] > $ts_start_time) || ($asRow['start_time'] < $ts_end_time && $asRow['end_time'] > $ts_end_time) || ($asRow['start_time'] <= $ts_start_time && $asRow['end_time'] >= $ts_end_time) || ($asRow['start_time'] >= $ts_start_time && $asRow['end_time'] <= $ts_end_time))
											{
												$valid_for_this_day = FALSE;
												if (!$aToken)
												{
													$cReason .= "conflicts with an already scheduled section for this instructor;<br/>";
													$aToken = TRUE;
												}
											}
										}
									}
								}
								
								//if we're scheduling honor courses, check to see if there is already an honors course
								// scheduled for a time that overlaps this timeslot
								if ($schedule_honors)
								{
									$alreadyScheduledQuery = "SELECT *
															  FROM schedule 
															  JOIN course_sections cs ON cs.id = schedule.course_section_id
															  JOIN time_slots ts ON ts.id = schedule.time_slot_id
															  WHERE honors_program = '1'
															  AND semester_id = '$semester_id'";
									$alreadyScheduledResult = mysql_query($alreadyScheduledQuery);
									while ($asRow = mysql_fetch_assoc($alreadyScheduledResult))
									{
										if ($asRow[$day] == 1)
										{
											if (($asRow['start_time'] < $ts_start_time && $asRow['end_time'] > $ts_start_time) || ($asRow['start_time'] < $ts_end_time && $asRow['end_time'] > $ts_end_time) || ($asRow['start_time'] <= $ts_start_time && $asRow['end_time'] >= $ts_end_time) || ($asRow['start_time'] >= $ts_start_time && $asRow['end_time'] <= $ts_end_time))
											{
												$valid_for_this_day = FALSE;
												if (!$hToken)
												{
													$cReason .= "Strict scheduling with regards to honors is ON -> Timeslot overlaps with scheduled honors course; <br/>";
													$hToken = TRUE;
												}
											}
										}
									}
								}
								
								if ($schedule_levels)
								{
									$course_level = substr($course_number, 0, 1);
									$min_course_level = $course_level."000";
									$max_course_level = $course_level."999";
									$alreadyScheduledQuery = "SELECT *
															  FROM schedule 
															  JOIN course_sections cs ON cs.id = schedule.course_section_id
															  JOIN course_section_mapping csm ON csm.course_section_id = cs.id
															  JOIN courses c ON c.id = csm.course_id
															  JOIN time_slots ts ON ts.id = schedule.time_slot_id
															  WHERE course_number >= '$min_course_level'
															  AND course_number <= '$max_course_level'
															  AND semester_id = '$semester_id'";
									$alreadyScheduledResult = mysql_query($alreadyScheduledQuery);
									//echo mysql_error();
									while ($asRow = mysql_fetch_assoc($alreadyScheduledResult))
									{
										if ($asRow[$day] == 1)
										{
											if (($asRow['start_time'] < $ts_start_time && $asRow['end_time'] > $ts_start_time) || ($asRow['start_time'] < $ts_end_time && $asRow['end_time'] > $ts_end_time) || ($asRow['start_time'] <= $ts_start_time && $asRow['end_time'] >= $ts_end_time) || ($asRow['start_time'] >= $ts_start_time && $asRow['end_time'] <= $ts_end_time))
											{
												$valid_for_this_day = FALSE;
												if (!$LToken)
												{
													$cReason .= "Strict scheduling with regards to course level is ON -> Timeslot overlaps with scheduled course of same level;<br/>";
													$LToken = TRUE;
												}
											}
										}
									}
								}
								
								//check to see if the course has a lab.
								//if it does have a lab, check that this timeslot does not overlap
								// with an associated, already scheduled course
								$labQuery = "SELECT *
											 FROM schedule s
											 JOIN time_slots ts ON ts.id = s.time_slot_id
											 JOIN course_sections cs ON cs.id = s.course_section_id
											 JOIN course_section_mapping csm ON csm.course_section_id = cs.id
											 JOIN courses c ON c.id = csm.course_id
											 WHERE c.id IN (SELECT c1.id 
															   FROM courses c1
															   JOIN courses c2 ON c2.lab_number = c1.id
															   WHERE c2.course_number = '$course_number')";
								$labResult = mysql_query($labQuery);
								while ($labRow = mysql_fetch_assoc($alreadyScheduledResult))
								{
									if ($labRow[$day] == 1)
									{
										if (($labRow['start_time'] < $ts_start_time && $labRow['end_time'] > $ts_start_time) || ($labRow['start_time'] < $ts_end_time && $labRow['end_time'] > $ts_end_time) || ($labRow['start_time'] <= $ts_start_time && $labRow['end_time'] >= $ts_end_time) || ($labRow['start_time'] >= $ts_start_time && $labRow['end_time'] <= $ts_end_time))
										{
											$valid_for_this_day = FALSE;
											if (!$hasLabToken)
											{
												$cReason .= "A lab for this course is scheduled for a time which overlaps with this timeslot.<br/>";
												$hasLabToken = TRUE;
											}
										}
									}
								}
								
								//check to see if the course IS a lab.
								//if it is a lab, check that this timeslot does not overlap
								// with an associated, already scheduled course
								$labQuery = "SELECT *
											 FROM schedule s
											 JOIN time_slots ts ON ts.id = s.time_slot_id
											 JOIN course_sections cs ON cs.id = s.course_section_id
											 JOIN course_section_mapping csm ON csm.course_section_id = cs.id
											 JOIN courses c ON c.id = csm.course_id
											 WHERE c.lab_number IN (SELECT c1.lab_number 
																	FROM courses c1
																	JOIN courses c2 ON c1.lab_number = c2.id
																	WHERE c2.course_number = '$course_number')";
								$labResult = mysql_query($labQuery);
								while ($labRow = mysql_fetch_assoc($alreadyScheduledResult))
								{
									if ($labRow[$day] == 1)
									{
										if (($labRow['start_time'] < $ts_start_time && $labRow['end_time'] > $ts_start_time) || ($labRow['start_time'] < $ts_end_time && $labRow['end_time'] > $ts_end_time) || ($labRow['start_time'] <= $ts_start_time && $labRow['end_time'] >= $ts_end_time) || ($labRow['start_time'] >= $ts_start_time && $labRow['end_time'] <= $ts_end_time))
										{
											$valid_for_this_day = FALSE;
											if (!$isLabToken)
											{
												$cReason .= "This is a lab for a course which is scheduled for a time that overlaps with this timeslot.<br/>";
												$isLabToken = TRUE;
											}
										}
									}
								}
								
								
								if (!$valid_for_this_day)
								{
									$valid_timeslot = FALSE;
								}
							}
						}
						
						/*If the timeslot fits the instructor's schedule and doesn't overlap with an already
						scheduled sections, push it on the array of valid timeslots for this section and instructor*/
						if ($valid_timeslot)
						{
							array_push($valid_timeslots, $timeslotRow['id']);	
							//echo $timeslotRow['id'].', ';
						}
						else
						{
							$cDescription = "<strong>Timeslot not valid due to:</strong><br/>".$cReason;
							$cDescription = mysql_real_escape_string($cDescription);
							$collisionQuery .= " ('', '$course_section_id', '$timeslotRow[id]', '$cDescription', '$date_time'),";
						}
						
						
					}
					
					
					if (preg_match('/Timeslot not valid/', $collisionQuery) == 1)
					{
						$collisionQuery = substr_replace($collisionQuery, '', -1, 1);
						$collisonResult = mysql_query($collisionQuery);
						//echo "<br/><br/>".$collisionQuery."<br/>".mysql_error()."<br/><br/>";
					}
				}
				
				/*loop through the valid timeslots*/
				foreach ($valid_timeslots as $valid_timeslot_id)
				{
					$tsQuery = "SELECT *
							    FROM time_slots
							    WHERE id = '$valid_timeslot_id'";
					$tsResult = mysql_query($tsQuery);
					$tsRow = mysql_fetch_assoc($tsResult);
					$ts_start_time = $tsRow['start_time'];
					$ts_end_time = $tsRow['end_time'];
					$ts_Monday = $tsRow['Monday'];
					$ts_Tuesday = $tsRow['Tuesday'];
					$ts_Wednesday = $tsRow['Wednesday'];
					$ts_Thursday = $tsRow['Thursday'];
					$ts_Friday = $tsRow['Friday'];
					$ts_Saturday = $tsRow['Saturday'];
					$ts_Sunday = $tsRow['Sunday'];
					
					
					/*get all classrooms in the building that have enough seats for the section*/
					$classroomQuery = "SELECT id
									   FROM classrooms 
									   WHERE building_id = '$building_id'
									   AND num_seats >= '$num_seats'";
					/*if $strict_classroom_type equals 1, only select classrooms of that classroom type*/
					if ($strict_classroom_type)
					{
						$classroomQuery .= " AND classroom_type_id = '$classroom_type_id'";
					}
					/*order smaller classrooms first so that you don't schedule a small section in a large room
					unless completely necessary*/
					$classroomQuery .= " ORDER BY num_seats ASC";
					$classroomResult = mysql_query($classroomQuery);
					
					$date_time = date('Y-m-d H:i:s');
					$collisionQuery = "INSERT INTO collision_log (id, section_id, timeslot_id, classroom_id, description, date_time) VALUES ";
					
					while ($crRow = mysql_fetch_assoc($classroomResult))
					{
						$cReason = '';
						$scToken = FALSE;
						$ccToken = FALSE;
						
						$classroom_id = $crRow['id'];
						$classroom_available = TRUE;
						
						$scheduledCoursesQuery = "SELECT * FROM schedule sch
												  JOIN time_slots ts ON ts.id = sch.time_slot_id
												  WHERE classroom_id = '$classroom_id'";
						$scheduledCoursesResult = mysql_query($scheduledCoursesQuery);
						while ($scRow = mysql_fetch_assoc($scheduledCoursesResult))
						{
							$sc_start_time = $scRow['start_time'];
							$sc_end_time = $scRow['end_time'];
							$sc_Monday = $scRow['Monday'];
							$sc_Tuesday = $scRow['Tuesday'];
							$sc_Wednesday = $scRow['Wednesday'];
							$sc_Thursday = $scRow['Thursday'];
							$sc_Friday = $scRow['Friday'];
							$sc_Saturday = $scRow['Saturday'];
							$sc_Sunday = $scRow['Sunday'];
							
							/* if the timelsot we're looking at and the scheduled course's timeslot are both
							scheduled on the same day, check to see if the times overlap */
							if (($ts_Monday == 1 && $sc_Monday == 1) || ($ts_Tuesday == 1 && $sc_Tuesday == 1) || ($ts_Wednesday == 1 && $sc_Wednesday == 1) || ($ts_Thursday == 1 && $sc_Thursday == 1) || ($ts_Friday == 1 && $sc_Friday == 1) || ($ts_Saturday == 1 && $sc_Saturday == 1) || ($ts_Sunday == 1 && $sc_Sunday == 1))
							{
								if (($sc_start_time < $ts_start_time && $sc_end_time > $ts_start_time) || ($sc_start_time < $ts_end_time && $sc_end_time > $ts_end_time) || ($sc_start_time <= $ts_start_time && $sc_end_time >= $ts_end_time) || ($sc_start_time >= $ts_start_time && $sc_end_time <= $ts_end_time))
								{
									$classroom_available = FALSE;
									if (!$scToken)
									{
										$cReason .= "a course is already scheduled in this classroom at a time which overlaps with the timeslot;<br />";
										$scToken = TRUE;
									}
								}
							}
						}
						
						/*Check for classroom closings*/
						$closingQuery = "SELECT *
									     FROM classroom_closings cc
										 JOIN days on days.id = cc.day_id
										 WHERE classroom_id = '$classroom_id'";
						$closingResult = mysql_query($closingQuery);
						while ($closingRow = mysql_fetch_assoc($closingResult))
						{
							$c_day_name = $closingRow['day_name'];
							$c_start_time = $closingRow['start_time'];
							$c_end_time = $closingRow['end_time'];
							
							if (${'ts_'.$c_day_name} == 1)
							{
								if (($c_start_time < $ts_start_time && $c_end_time > $ts_start_time) || ($c_start_time < $ts_end_time && $c_end_time > $ts_end_time) || ($c_start_time <= $ts_start_time && $c_end_time >= $ts_end_time) || ($c_start_time >= $ts_start_time && $c_end_time <= $ts_end_time))
								{
									$classroom_available = FALSE;
									if (!$ccToken)
									{
										$cReason .= "this classroom is closed for a time which overlaps with the timeslot;<br />";
										$ccToken = TRUE;
									}
								}
							}
						}
						
						
						if ($classroom_available)
						{
							$scheduleQuery = "INSERT INTO schedule (id, course_section_id, classroom_id, time_slot_id)
											  VALUES ('', '$course_section_id', '$classroom_id', '$valid_timeslot_id')";
							$scheduleResult = mysql_query($scheduleQuery);
							//echo $scheduleQuery."<br/><br/>";
							
							goto skip_to_next_section;
						}
						else
						{
							$cDescription = "<strong>Classroom not available for this timeslot due to:</strong><br/>".$cReason;
							$cDescription = mysql_real_escape_string($cDescription);
							$collisionQuery .= " ('', '$course_section_id', '$valid_timeslot_id', '$classroom_id', '$cDescription', '$date_time'),";
						}
						
					}
				}
			
			skip_to_next_section:
				if (preg_match('/Classroom not available/', $collisionQuery) == 1)
				{
					$collisionQuery = substr_replace($collisionQuery, '', -1, 1);
					$collisonResult = mysql_query($collisionQuery);
					//echo "<br/><br/>".$collisionQuery."<br/>".mysql_error()."<br/><br/>";
				}
				continue;
			}
		}
	}
	
	/*
	initial_schedule($building_id,1,0,1);
	initial_schedule($building_id,1,0,0);
	initial_schedule($building_id,0,1,0);
	initial_schedule($building_id,0,0,1);
	initial_schedule($building_id,0,0,0);
	second_pass($building_id);
	*/
	//echo "<h1>Pass 1</h1>";
	initial_schedule($semester_id,$building_id,1,0,1,1);
	//echo "<h1>Pass 2</h1>";
	initial_schedule($semester_id,$building_id,1,0,0,1);
	//echo "<h1>Pass 3</h1>";
	initial_schedule($semester_id,$building_id,1,0,0,0);
	//echo "<h1>UNDERGRAD</h1><h1>Pass 4</h1>";
	initial_schedule($semester_id,$building_id,0,1,1,1);
	//echo "<h1>Pass 5</h1>";
	initial_schedule($semester_id,$building_id,0,1,0,1);
	//echo "<h1>Pass 6</h1>";
	initial_schedule($semester_id,$building_id,0,1,0,0);
	//echo "<h1>Pass 7</h1>";
	initial_schedule($semester_id,$building_id,0,0,1,1);
	//echo "<h1>Pass 8</h1>";
	initial_schedule($semester_id,$building_id,0,0,0,1);
	//echo "<h1>Pass 9</h1>";
	initial_schedule($semester_id,$building_id,0,0,0,0);
	header("Location:postSchedule.php?semester_id=$semester_id&building_id=$building_id");
?>